-- @owner: @wan005
-- @date: 2022/09/20
-- @testpoint: 作为关键字测试，验证是否可以作为表名、列名、函数名、存储过程名、视图名等
-- @modified: by @yangyixiang 2023/03/27 因系统函数schema位置变更且仍对用户可见，drop时需指定schema

--step1:关键字测试; expect:可以作为关键字
create schema schema_unsigned_0029;
set current_schema to schema_unsigned_0029;

--point1:表名
drop table if exists uint1;
create table uint1 (a uint1);
drop table if exists uint2;
create table uint2 (a uint2);
drop table if exists uint4;
create table uint4 (a uint4);
drop table if exists uint8;
create table uint8 (a uint8);
drop table if exists tinyint_unsigned;
create table tinyint_unsigned (a tinyint unsigned);
drop table if exists smallint_unsigned;
create table smallint_unsigned (a smallint unsigned);
drop table if exists int_unsigned;
create table int_unsigned (a int unsigned);
drop table if exists bigint_unsigned;
create table bigint_unsigned (a uint8);

drop table if exists uint1;
drop table if exists uint2;
drop table if exists uint4;
drop table if exists uint8;
drop table if exists tinyint_unsigned;
drop table if exists smallint_unsigned;
drop table if exists int_unsigned;
drop table if exists bigint_unsigned;

--point2:列名
drop table if exists t_unsigned_0029;
create table t_unsigned_0029 (uint1 uint1,uint2 uint2,uint4 uint4,uint8 uint8);
drop table if exists t_unsigned_0029;

--point3:函数名
drop function if exists schema_unsigned_0029.uint1();
drop function if exists schema_unsigned_0029.uint2();
drop function if exists schema_unsigned_0029.uint4();
drop function if exists schema_unsigned_0029.uint8();
create function uint1(in uint1,
out f1 uint1, out f2 uint2, out f3 uint4, out f4 uint8)
as $$
select $1, cast($1 as uint2), cast($1 as uint4), cast($1 as uint8)
$$
language sql;
/
create function uint2(in uint1,
out f1 uint1, out f2 uint2, out f3 uint4, out f4 uint8)
as $$
select $1, cast($1 as uint2), cast($1 as uint4), cast($1 as uint8)
$$
language sql;
/
create function uint4(in uint1,
out f1 uint1, out f2 uint2, out f3 uint4, out f4 uint8)
as $$
select $1, cast($1 as uint2), cast($1 as uint4), cast($1 as uint8)
$$
language sql;
/
create function uint8(in uint1,
out f1 uint1, out f2 uint2, out f3 uint4, out f4 uint8)
as $$
select $1, cast($1 as uint2), cast($1 as uint4), cast($1 as uint8)
$$
language sql;
/
drop function if exists schema_unsigned_0029.uint1();
drop function if exists schema_unsigned_0029.uint2();
drop function if exists schema_unsigned_0029.uint4();
drop function if exists schema_unsigned_0029.uint8();

--point4:存储过程名
drop procedure if exists schema_unsigned_0029.uint1;
drop procedure if exists schema_unsigned_0029.uint2;
drop procedure if exists schema_unsigned_0029.uint4;
drop procedure if exists schema_unsigned_0029.uint8;
create or replace procedure uint1() as select_str varchar;
begin
select_str = 'select (-1)::uint1;';
execute immediate select_str;
end;
/
create or replace procedure uint2() as select_str varchar;
begin
select_str = 'select (-1)::uint2;';
execute immediate select_str;
end;
/
create or replace procedure uint4() as select_str varchar;
begin
select_str = 'select (-1)::uint4;';
execute immediate select_str;
end;
/
create or replace procedure uint8() as select_str varchar;
begin
select_str = 'select (-1)::uint8;';
execute immediate select_str;
end;
/
drop procedure if exists schema_unsigned_0029.uint1;
drop procedure if exists schema_unsigned_0029.uint2;
drop procedure if exists schema_unsigned_0029.uint4;
drop procedure if exists schema_unsigned_0029.uint8;

--point5:视图名
drop view if exists uint1;
drop view if exists uint2;
drop view if exists uint4;
drop view if exists uint8;
create view uint1 as select 84651;
create view uint2 as select 84651;
create view uint4 as select 84651;
create view uint8 as select 84651;
drop view if exists uint1;
drop view if exists uint2;
drop view if exists uint4;
drop view if exists uint8;

--point6:索引名
drop table if exists t_unsigned_0029;
create table t_unsigned_0029(a uint1);
drop index if exists uint1,uint2,uint4,uint8;
create index uint1 on t_unsigned_0029(a);
create index uint2 on t_unsigned_0029(a);
create index uint4 on t_unsigned_0029(a);
create index uint8 on t_unsigned_0029(a);
drop index if exists uint1,uint2,uint4,uint8;
drop table if exists t_unsigned_0029;

--point7:数据库名
drop database if exists uint1;
drop database if exists uint2;
drop database if exists uint4;
drop database if exists uint8;
create database uint1;
create database uint2;
create database uint4;
create database uint8;
drop database if exists uint1;
drop database if exists uint2;
drop database if exists uint4;
drop database if exists uint8;

--point8:用户名
drop user if exists uint1;
drop user if exists uint2;
drop user if exists uint4;
drop user if exists uint8;
create user uint1 password 'zxcvbnm0*96';
create user uint2 password 'zxcvbnm0*96';
create user uint4 password 'zxcvbnm0*96';
create user uint8 password 'zxcvbnm0*96';
drop user if exists uint1;
drop user if exists uint2;
drop user if exists uint4;
drop user if exists uint8;

--point9:组名
drop group if exists uint1;
drop group if exists uint2;
drop group if exists uint4;
drop group if exists uint8;
create group uint1 password 'zxcvbnm0*96';
create group uint2 password 'zxcvbnm0*96';
create group uint4 password 'zxcvbnm0*96';
create group uint8 password 'zxcvbnm0*96';
drop group if exists uint1;
drop group if exists uint2;
drop group if exists uint4;
drop group if exists uint8;

--point10:角色名
drop role if exists uint1;
drop role if exists uint2;
drop role if exists uint4;
drop role if exists uint8;
create role uint1 password 'zxcvbnm0*96';
create role uint2 password 'zxcvbnm0*96';
create role uint4 password 'zxcvbnm0*96';
create role uint8 password 'zxcvbnm0*96';
drop role if exists uint1;
drop role if exists uint2;
drop role if exists uint4;
drop role if exists uint8;

--point11:序列名
drop sequence if exists uint1;
drop sequence if exists uint2;
drop sequence if exists uint4;
drop sequence if exists uint8;
create sequence uint1 start 101 cache 20;
create sequence uint2 start 101 cache 20;
create sequence uint4 start 101 cache 20;
create sequence uint8 start 101 cache 20;
drop sequence if exists uint1;
drop sequence if exists uint2;
drop sequence if exists uint4;
drop sequence if exists uint8;

--step2:清理环境; expect:成功
drop schema schema_unsigned_0029 cascade;